package zhuzx.temp;

import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.zhuzx.util.FileOperationUtil;
import org.zhuzx.util.GeneralUtil;
import org.zhuzx.util.JdbcUtil;
import org.zhuzx.util.MicroUtil;

public class SpAccountSqlGenerator {
	
	static Connection conn = JdbcUtil.getDbConn(null);
	
	public static void main(String[] args) throws IOException, SQLException {
		try {
			rollMonth7();
		} finally {
			conn.close();
		}
	}
	
	static void rollMonth7() throws IOException {
		String rollLogFile = "C:\\Users\\zhuzexiang\\Desktop\\sql脚本-6月份的错误工厂优惠回算.txt";
		List<String> temp = Files.readAllLines(Paths.get(rollLogFile));
        List<String> rollSqls = new ArrayList<>();
        for (String line : temp) {
        	//这个是6月份算错了工厂的，把这些记录捞出来，查到相关的7月份的记录，把7月份的两个结余再扣减一次。
        	String querySql = "select * from OA.SP_ACCOUNT_BILL t where id = '" +
        			MicroUtil.substringByMates(line, '\'', '\'') + "'";
        	Map<String,String> wrongRec = JdbcUtil.executeQuery(querySql, conn).get(0);
        	String clause = " where t.customer_no = '${CUSTOMER_NO}' and t.bill_year = '${BILL_YEAR}'"
					+ " and t.bill_month = '07' and t.factory_no = '${FACTORY_NO}'";
        	clause = MicroUtil.fillParameters(clause, wrongRec);
        	String testSql = "select * from OA.SP_ACCOUNT_BILL t" + clause;
        	List<Map<String, String>> testResult;
			try {
				testResult = JdbcUtil.executeQuery(testSql, conn);
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
        	if (testResult.size() != 1) {
        		throw new RuntimeException("结果不唯一：" + testSql);
        	}
        	String amount = MicroUtil.substringByMatesOnRegex(line, "AVAILABLE_BALANCE - ", ",");
        	String sql = "update OA.SP_ACCOUNT_BILL t set previous_balance = previous_balance - " + amount
        			+ ", available_balance = available_balance - " + amount + clause;
        	rollSqls.add(sql + ";");
        }
        FileOperationUtil.writeToFile(GeneralUtil.getCombinedString(rollSqls, "\n")
                .getBytes(StandardCharsets.UTF_8), "回算7月份的错误工厂优惠结余.txt", true);
        
        String addLogFile = "C:\\Users\\zhuzexiang\\Downloads\\tips.log.2021-07-02";
        String addLogs = new String(Files.readAllBytes(Paths.get(addLogFile)), StandardCharsets.UTF_8);
        List<String> addSqls = new ArrayList<>();
        String[] lines = addLogs.split("\n");
        for (int i=0; i<lines.length; i++) {
        	String getBill = lines[i];
        	String querySql = MicroUtil.subBehind(getBill, "的getBill语句：");
        	if (querySql == null) {
        		continue;
        	} else {
        		querySql = querySql.replace("bill_month = '06'", "bill_month = '07'");
        	}
        	List<Map<String,String>> testResult = JdbcUtil.executeQuery(querySql, conn);
        	if (testResult.size() != 1) {
        		throw new RuntimeException("结果不唯一：" + querySql);
        	}
        	String updM6 = lines[i+2];
        	String amount = MicroUtil.substringByMates(updM6, "AVAILABLE_BALANCE + ", ",");
        	String addUpd = "update OA.SP_ACCOUNT_BILL t set previous_balance = previous_balance + " + amount
        			+ ", available_balance = available_balance + " + amount;
        	String sql = querySql.replace("select * from OA.SP_ACCOUNT_BILL t", addUpd);
        	addSqls.add(sql + ";");
        }
        FileOperationUtil.writeToFile(GeneralUtil.getCombinedString(addSqls, "\n")
                .getBytes(StandardCharsets.UTF_8), "加上7月份的正确工厂优惠结余.txt", true);
	}
	
	static void backup1() throws IOException {
        String logFile = "C:\\Users\\zhuzexiang\\Downloads\\tips.log.2021-06-10.txt";
        List<String> temp = Files.readAllLines(Paths.get(logFile));
        List<String> sqls = new ArrayList<>();
        for (String line : temp) {
                if (line.startsWith("update OA.SP_ACCOUNT_BILL set AVAILABLE_BALANCE")) {
                        sqls.add(line + ";");
                }
        }
        FileOperationUtil.writeToFile(GeneralUtil.getCombinedString(sqls, "\n")
                        .getBytes(StandardCharsets.UTF_8), "ori_sqls.txt", true);
        for (int i=0; i<sqls.size(); i++) {
                String line = sqls.get(i);
                line = line.replace("+", "_+_");
                line = line.replace("-", "+");
                line = line.replace("_+_", "-");
                sqls.set(i, line);
        }
        FileOperationUtil.writeToFile(GeneralUtil.getCombinedString(sqls, "\n")
                        .getBytes(StandardCharsets.UTF_8), "result_sqls.txt", true);
	}

}